import org.w3c.dom.Document;
import org.w3c.dom.Element;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class StudentManagement {
    private static String url;
    private static String username;
    private static String password;
    static{
        try{
            File xmlFile = new File("config.xml");
            DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
            Document doc = dBuilder.parse(xmlFile);
            doc.getDocumentElement().normalize();

            Element databaseElement=(Element)doc.getElementsByTagName("database").item(0);
            url = databaseElement.getElementsByTagName("url").item(0).getTextContent();
            username = databaseElement.getElementsByTagName("username").item(0).getTextContent();
            password = databaseElement.getElementsByTagName("password").item(0).getTextContent();
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection()throws SQLException{
        return DriverManager.getConnection(url,username,password);
    }
    public static void inserStudent(String number,String name,String grade,String clbum,String gender){
        String sql = "INSERT INTO students (number,name,grade,clbum,gender VALUES(?,?,?,?,?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)){
            pstmt.setString(1,number);
            pstmt.setString(2,name);
            pstmt.setString(3,grade);
            pstmt.setString(4,clbum);
            pstmt.setString(5,gender);
            pstmt.executeUpdate();
        }
        catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void updateStudent(String name,String newGrade){
        String sql = "UPDATE students SET grade =? WHERE name =?";
        try(Connection conn = getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql)){
            pstmt.setString(1,newGrade);
            pstmt.setString(2,name);
            pstmt.executeUpdate();
        }
        catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void queryStudentByNumber(String number)
    {
        String sql = "SELECT * FROM students WHERE number =?";
        try(Connection conn = getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql)){
            pstmt.setString(1,number);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                System.out.println("学号:"+ rs.getString("number"));
                System.out.println("姓名:"+ rs.getString("name"));
                System.out.println("年级:"+ rs.getString("grade"));
                System.out.println("班级:"+ rs.getString("clbum"));
                System.out.println("性别:"+ rs.getString("gender"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void queryAllStudents()
    {
        String sql = "SELECT * FROM students";
        try(Connection conn = getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery()){
            while(rs.next()){
                System.out.println("学号:"+ rs.getString("number"));
                System.out.println("姓名:"+ rs.getString("name"));
                System.out.println("年级:"+ rs.getString("grade"));
                System.out.println("班级:"+ rs.getString("clbum"));
                System.out.println("性别:"+ rs.getString("gender"));

                System.out.println("----------------------------------");
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
    public static void main(String[] args){
        inserStudent("","赵六","四年级","四班","男");
        updateStudent("赵六","五年级");
        queryStudentByNumber("004");
        queryAllStudents();
    }
}
